/**
 * ElectronExchanger Server Code File
 *
 * File:        db_helper.c
 * Brief:       BRIEF
 * Author:      Shen Fuyuan
 * Email:       ckblau@outlook.com
 * Modified:    2020/09/13 11:20:56
 *
 */

#include "db_helper.h"

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/*

int db_send_mail() {

}

int db_get_file() {
    int ret;
    int fid;
    DB_FileDataTypeDef file;

    // ...

    ret = mysql_get_file(fid, &file);

    // ...
}

int db_upload_file() {
    int ret;
    DB_FileDataTypeDef file;

    // ...

    ret = mysql_upload_file(&file);

    // ...
}

//*/

MYSQL *conn_prt;

MYSQL_RES *res;

MYSQL_ROW row;

char field_values[210000] = {0};
char insert_query[210000] = {0};

/********************Open Sql********************/

int mysql_OpenSql() {

    conn_prt = mysql_init(NULL);

    if (!mysql_real_connect(conn_prt, "localhost", "root", "Electron123", "Email", 0, NULL, 0)) {
        printf("Error connecting to database [%d]: %s", mysql_errno(conn_prt), mysql_error(conn_prt));
        return 1; //连接语句错误
    } else {
        return 0; //成功
    }
}

/********************Close Sql********************/

int mysql_CloseSql() {
    mysql_close(conn_prt);
    return 0;
}

/********************Check Sql********************/

int mysql_check(char *Table, char *name, char *values) {

    char check_query[1000] = {0};

    sprintf(check_query, "%s %s %s %s %s %s %s \"%s\"", "select", name, "from",
            Table, "where", name, " = ", values);

    if (mysql_real_query(conn_prt, check_query, strlen(check_query))) {
        return 1; //检查语句出错错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    if (row != NULL) {
        mysql_free_result(res);
        return 2; //表中已存在该数据
    } else {
        mysql_free_result(res);
        return 3; //表中不存在该数据
    }
}

/********************Get Server data********************/

int mysql_get_serverdata(char *name, int *values) {

    char get_query[1000];

    sprintf(get_query, "%s %s %s", "select", name, "from ServerData");

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    *values = atoi(row[0]);

    mysql_free_result(res);

    return 0; //成功
}

/********************Set Server data********************/

int mysql_set_serverdata(char *name, int data) {

    char update_query[1000];

    sprintf(update_query, "%s %s %s %d", "update ServerData set", name, "=",
            data);

    if (mysql_real_query(conn_prt, update_query, strlen(update_query))) {
        return 1; //更新语句错误
    } else {
        return 0; //成功
    }
}

/********************Register Sql********************/

int mysql_register(DB_UserDataTypeDef *user) {

    int ret = mysql_check("User", "UserEmail", user->UserEmail);
    if (ret == 1) {
        return 1; //检查语句错误
    } else if (ret == 2) {
        return 2; //已重复
    }

    char insert_query[1000] = {0};
    // char field_values[10000] = {0};
    char field_list[100] = {
        "(UserID, UserEmail, NickName, Password, Salt, RegistrationTime)"};

    sprintf(field_values, "%s%d,\"%s\",\"%s\",\"%s\",\"%s\",%d%s", "(",
            user->UserID, user->UserEmail, user->NickName, user->Password,
            user->Salt, user->RegistrationTime, ")");

    sprintf(insert_query, "%s%s %s %s", "insert into User", field_list,
            "values", field_values);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 3; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Create Session ID********************/

int mysql_create_session_id(char *session_id, int user_id, int expire_time) {

    char insert_query[1000] = {0};
    char field_values[1000] = {0};
    char field_list[100] = {"(SessionID, UserID , ExpireTime)"};

    sprintf(field_values, "%s\"%s\", %d, %d%s", "(", session_id, user_id,
            expire_time, ")");

    sprintf(insert_query, "%s%s %s %s", "insert into SessionData", field_list,
            "values", field_values);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 1; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Destroy Session ID********************/

int mysql_destroy_session_id(char *session_id) {

    char drop_query[1000] = {0};

    sprintf(drop_query, "%s \"%s\"",
            "delete from SessionData where SessionID =", session_id);

    if (mysql_real_query(conn_prt, drop_query, strlen(drop_query))) {
        return 1; //删除语句错误
    } else {
        return 0; //成功
    }
}

/********************Check Session ID********************/

int mysql_check_session_id(char *session_id, int *user_id, int *expire_time) {

    char get_query[1000] = {0};
    *user_id = -1;

    sprintf(get_query, "%s \"%s\"",
            "select UserID, ExpireTime from SessionData where SessionID =",
            session_id);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //检查语句出错错误
    }

    res = mysql_store_result(conn_prt);
    if (res == NULL) return 1;

    row = mysql_fetch_row(res);

    if (row == NULL) {
        mysql_free_result(res);
        return 3; //表中不存在该数据
    } else {
        *user_id = atoi(row[0]);
        *expire_time = atoi(row[1]);
        mysql_free_result(res);
        return 0; //成功
    }
}

/********************Refresh Session ID********************/

int mysql_refresh_session_id(char *session_id, int expire_time) {

    char update_query[1000];

    sprintf(update_query, "%s %d %s \"%s\"",
            "update SessionData set ExpireTime =", expire_time,
            "where SessionID =", session_id);

    if (mysql_real_query(conn_prt, update_query, strlen(update_query))) {
        return 1; //更新语句错误
    } else {
        return 0; //成功
    }
}

/********************Set Password********************/

int mysql_set_password(int uid, char *new_password, char *salt) {

    char set_query1[1000];
    sprintf(set_query1, "%s\"%s\"%s\"%s\"%s%d",
            "update User set Password = ", new_password,", Salt = ", salt,
            " where UserID = ", uid);

    if (mysql_real_query(conn_prt, set_query1, strlen(set_query1))) {
        return 1; //修改语句出错
    } else {
        return 0; //成功
    }

    
}

/********************Get Information********************/

int mysql_get_userinfo(int uid, DB_UserDataTypeDef *user) {

    char get_query[1000] = {0};

    sprintf(get_query, "%s %d", "select * from User where UserID =", uid);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句出错错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    if (row == NULL) {
        mysql_free_result(res);
        return 3; //表中不存在该数据
    } else {
        user->UserID = atoi(row[0]);
        strcpy(user->UserEmail, row[1]);
        strcpy(user->NickName, row[2]);
        strcpy(user->Password, row[3]);
        strcpy(user->Salt, row[4]);
        user->RegistrationTime = atoi(row[5]);
        mysql_free_result(res);
        return 0; //成功
    }
}

/********************Set NickName********************/

int mysql_set_nickname(int uid, char *nickname) {

    char update_query[1000];

    sprintf(update_query, "%s \"%s\" %s %d",
            "update User set NickName =", nickname, "where UserID =", uid);

    if (mysql_real_query(conn_prt, update_query, strlen(update_query))) {
        return 1; //更新语句错误
    } else {
        return 0; //成功
    }
}

/********************Get File********************/

int mysql_get_file(int fid, DB_FileDataTypeDef *file) {

    char get_query[1000] = {0};

    sprintf(get_query, "%s %d", "select * from File where FileID =", fid);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句出错错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    if (row == NULL) {
        mysql_free_result(res);
        return 3; //表中不存在该数据
    } else {
        file->FileID = atoi(row[0]);
        strcpy(file->FileName, row[1]);
        file->FileSize = atoi(row[2]);
        strcpy(file->FilePath, row[3]);
        file->FileTime = atoi(row[4]);
        mysql_free_result(res);
        return 0; //成功
    }
}

/********************Upload File********************/

int mysql_upload_file(DB_FileDataTypeDef *file) {

    char insert_query[1000] = {0};
    char field_values[2000] = {0};
    char field_list[100] = {"(FileID, FileName, FileSize, FilePath, FileTime)"};

    sprintf(field_values, "%s%d,\"%s\",%d,\"%s\",%d%s", "(", file->FileID,
            file->FileName, file->FileSize, file->FilePath, file->FileTime,
            ")");

    sprintf(insert_query, "%s%s %s %s", "insert into File", field_list,
            "values", field_values);

    printf("%s\n", insert_query);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 1; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Get Box Size********************/

int mysql_get_box_size(int uid, char *box, int *list_size) {
    // SELECT COUNT(*) FROM table_name
    char get_query[1000];

    sprintf(get_query, "%s %s %s %d", "select count(*) from", box,
            "where UserID =", uid);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    *list_size = atoi(row[0]);

    mysql_free_result(res);

    return 0;
}

/********************Set Contact NickName********************/

int mysql_set_contact_nickname(int uid, int cid, char *nickname) {

    char update_query[1000];

    sprintf(update_query, "%s \"%s\" %s %d %s %d",
            "update Contact set ContactNickName = ", nickname,
            "where UserID =", uid, "and ContactID =", cid);

    if (mysql_real_query(conn_prt, update_query, strlen(update_query))) {
        return 1; //更新语句错误
    } else {
        return 0; //成功
    }
}

/********************Add Contact********************/

int mysql_add_contact(DB_ContactDataTypeDef *contact) {

    // char insert_query[1000] = {0};
    // char field_values[10000] = {0};
    char field_list[100] = {"(UserID, ContactID, ContactNickName)"};

    sprintf(field_values, "%s%d,%d,\"%s\"%s", "(", contact->UserID,
            contact->ContactID, contact->ContactNickName, ")");

    sprintf(insert_query, "%s%s %s %s", "insert into Contact", field_list,
            "values", field_values);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 1; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Delete Contact********************/

int mysql_delete_contact(int uid, int cid) {

    char drop_query[1000] = {0};

    sprintf(drop_query, "%s %d %s %d",
            "delete from Contact where UserID =", uid, "and ContactID =", cid);

    if (mysql_real_query(conn_prt, drop_query, strlen(drop_query))) {
        return 1; //删除语句错误
    } else {
        return 0; //成功
    }
}

/********************Get Target UserID********************/

int mysql_get_target_userid(char *email, int *target_uid) {

    char get_query[1000];

    sprintf(get_query, "%s \"%s\"",
            "select UserID from User where UserEmail = ", email);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句错误
    }

    res = mysql_store_result(conn_prt);
    if (res == NULL) return 1; //查询语句错误

    row = mysql_fetch_row(res);

    if (row == NULL) return 2;

    *target_uid = atoi(row[0]);

    mysql_free_result(res);

    return 0; //成功
}

/********************Get Target NickName********************/

int mysql_get_target_nickname(int target_uid, char *nickname) {

    char get_query[1000];

    sprintf(get_query, "%s %d",
            "select NickName from User where UserID = ", target_uid);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    strcpy(nickname, row[0]);

    mysql_free_result(res);

    return 0; //成功
}

/********************Get Mail List********************/

int mysql_get_mail_list(int uid, char *box, DB_EmailListTypeDef *list, int *list_size) {

    char get_query[1000] = {0};
    char get_list[100] = {"EmailNo, Subject, Time from Email where EmailNo in"};

    sprintf(get_query, "%s %s %s %s %s %d%s", "select", get_list,
            "( select EmailNo from", box, "where UserID =", uid, ")");

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句错误
    }

    res = mysql_store_result(conn_prt);

    int flag = 0;
    int i = 0;
    while ((row = mysql_fetch_row(res))) {
        if (row == NULL && flag == 0) {
            mysql_free_result(res);
            *list_size = 0;
            return 0; //表中无数据
        } else {
            flag = 1;
            list[i].EmailNo = atoi(row[0]);
            strcpy(list[i].Subject, row[1]);
            list[i].Time = atoi(row[2]);
            list[i].ReadState = 1;
        }
        i++;
    }

    *list_size = i;

    mysql_free_result(res);

    if (strcmp(box, "Inbox") == 0) {

        sprintf(get_query, "%s %d",
                "select ReadState from Inbox where UserID =", uid);

        if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
            return 2; //查询读取情况语句出错错误
        }

        res = mysql_store_result(conn_prt);

        i = 0;
        while ((row = mysql_fetch_row(res))) {
            list[i].ReadState = atoi(row[0]);
            i++;
        }

        mysql_free_result(res);

        return 0; //成功
    }

    return 0;
}

/********************Get Mail********************/

int mysql_get_mail(int eno, DB_EmailInfoTypeDef *mail, char *content) {

    char get_query[1000] = {0};

    sprintf(get_query, "%s %d", "select * from Email where EmailNo =", eno);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句出错错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    if (row == NULL) {
        mysql_free_result(res);
        return 2; //表中不存在该数据
    } else {
        mail->EmailNo = atoi(row[0]);
        strcpy(mail->FromEmail, row[1]);
        strcpy(mail->ToEmails, row[2]);
        strcpy(mail->BCCEmails, row[3]);
        strcpy(mail->Subject, row[4]);
        if (content != NULL) strcpy(content, row[5]);
        mail->FileID = atoi(row[6]);
        mail->Time = atoi(row[7]);
        mysql_free_result(res);
        return 0; //成功
    }
}

/********************Check Mail Exist********************/

int mysql_check_mail_exist(int uid, int eno, char *box, int *exist) {

    char get_query[1000] = {0};

    sprintf(get_query, "select * from %s where UserID = %d and EmailNo = %d", box, uid, eno);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句出错错误
    }

    res = mysql_store_result(conn_prt);

    row = mysql_fetch_row(res);

    if (row == NULL) {
        *exist = 0;
    } else {
        *exist = 1;
    }

    mysql_free_result(res);
    return 0; //成功
}

/********************Upload Mail********************/

int mysql_upload_mail(DB_EmailInfoTypeDef *mail, char *content) {

    char field_list[100] = {"(EmailNo, FromEmail, ToEmails, BCCEmails, "
                            "Subject, Content, FileID, Time)"};

    sprintf(field_values, "%s%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",%d,%d%s",
            "(", mail->EmailNo, mail->FromEmail, mail->ToEmails,
            mail->BCCEmails, mail->Subject, content, mail->FileID,
            mail->Time, ")");

    sprintf(insert_query, "%s%s %s %s", "insert into Email", field_list,
            "values", field_values);

    printf("%s\n", insert_query);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 1; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Save Mail********************/

int mysql_save_mail(int eno, int uid, char *box) {

    // char insert_query[1000] = {0};
    // char field_values[10000] = {0};
    char field_list[100] = {"(UserID, EmailNo)"};

    sprintf(field_values, "%s%d,%d%s", "(", uid, eno, ")");

    sprintf(insert_query, "%s %s%s %s %s", "insert into", box, field_list,
            "values", field_values);

    if (mysql_real_query(conn_prt, insert_query, strlen(insert_query))) {
        return 1; //插入语句错误
    } else {
        return 0; //成功
    }
}

/********************Delete Mail********************/

int mysql_delete_mail(int eno, int uid, char *box) {

    char drop_query[1000] = {0};

    sprintf(drop_query, "%s %s %s %d %s %d", "delete from", box,
            "where UserID =", uid, "and EmailNo =", eno);

    if (mysql_real_query(conn_prt, drop_query, strlen(drop_query))) {
        return 1; //删除语句错误
    } else {
        return 0; //成功
    }
}

/********************Get Contact List********************/

int mysql_get_contact_list(int uid, DB_ContactDataTypeDef *contact_list, int *list_size) {

    char get_query[1000] = {0};

    sprintf(get_query, "%s %d", "select * from Contact where UserID =", uid);

    if (mysql_real_query(conn_prt, get_query, strlen(get_query))) {
        return 1; //查询语句出错错误
    }

    res = mysql_store_result(conn_prt);

    int flag = 0; //判断是否为空
    int i = 0;
    while ((row = mysql_fetch_row(res))) {
        if (row == NULL && flag == 0) {
            mysql_free_result(res);
            return 3; //表中无数据
        } else {
            flag = 1;
            contact_list[i].UserID = atoi(row[0]);
            contact_list[i].ContactID = atoi(row[1]);
            strcpy(contact_list[i].ContactNickName, row[2]);
        }
        i++;
    }
    *list_size = i;

    mysql_free_result(res);

    return 0;
}

/********************Set Inbox Readstate********************/

int mysql_set_read_state(int uid, int eno, int read_state) {

    char set_query[1000] = {0};

    sprintf(set_query, "%s %d %s %d %s %d",
            "update Inbox set ReadState =", read_state, "where EmailNo =", eno,
            "and UserID =", uid);

    printf("%s", set_query);

    if (mysql_real_query(conn_prt, set_query, strlen(set_query))) {
        return 1; //查询语句出错错误
    }

    return 0;
}